package com.jackrain.nea.web.controller;

import com.alibaba.dubbo.config.annotation.Reference;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jackrain.nea.core.query.ColumnLink;
import com.jackrain.nea.core.schema.Column;
import com.jackrain.nea.core.schema.Table;
import com.jackrain.nea.core.schema.TableImpl;
import com.jackrain.nea.core.schema.TableManager;
import com.jackrain.nea.exception.NDSException;
import com.jackrain.nea.util.*;
import com.jackrain.nea.web.ExportCmd;
import com.jackrain.nea.web.common.AjaxUtils;
import com.jackrain.nea.web.common.Handle;
import com.jackrain.nea.web.common.WebIntercept;
import com.jackrain.nea.web.face.User;
import com.jackrain.nea.web.query.DefaultWebEvent;
import com.jackrain.nea.web.query.QueryRequest;
import com.jackrain.nea.web.query.QuerySession;
import com.jackrain.nea.web.query.QuerySessionImpl;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.UUID;

@Slf4j
@RestController
@Api(value = "ExportController", description = "数据导出")
public class ExportController {

    @Reference(version = "1",group = "ad")
    private ExportCmd exportCmd1;

    @Reference(version = "2",group = "cp")
    private ExportCmd exportCmd2;

    @Reference(version = "3",group = "ps")
    private ExportCmd exportCmd3;

    @Reference(version = "4",group = "vp")
    private ExportCmd exportCmd4;

    @Autowired
    private Handle handle;



    private final static int MAX_REPORT_LINES= 65535; //最大导出行数

    private final static  int max_rowcount = 100;//每页条数

    private String service = "com.jackrain.nea.web.ExportCmd";

    public JSONObject getData(Table table,QueryRequest query) throws Exception {
        String center  = table.getCategory().getSubSystem().getCenter();
        String[] gv = center.split(":");
        if(gv.length != 2){
            throw new NDSException("center is error");
        }
        Object o = ReferenceUtil.refer(ApplicationContextHandle.getApplicationContext(), service,gv[0],gv[1]);
        log.info("edas service->"+service+":"+gv[0]+":"+gv[1]);
        JSONObject result = ((ExportCmd) o).getData(query);
        return result;
    }

//    public QueryRequest parseQuery(JSONObject jo, QuerySession session, int userId, Locale locale,TableImpl table) throws Exception  {
//        String center  = table.getCategory().getSubSystem().getCenter();
//        String[] gv = center.split(":");
//        if(gv.length != 2){
//            throw new NDSException("center is error");
//        }
//        Object o = ReferenceUtil.refer(ApplicationContextHandle.getApplicationContext(), service,gv[0],gv[1]);
//        log.info("edas service->"+service+":"+gv[0]+":"+gv[1]);
//        QueryRequest request = ((ExportCmd) o).parseQuery(jo,session,userId,locale,table);
//        return request;
//    }



    /**
     * From 2.0, add two parameter in event:
     * "request" - QueryRequest of the original query
     */
    @RequestMapping(path = "/p/cs/export",method = {RequestMethod.POST,RequestMethod.GET})
    public JSONObject export(HttpServletRequest request,
                             @RequestParam(value = "searchdata", required = true) String searchdata,
                             @RequestParam(value = "filename", required = true) String fileName,
                             @RequestParam(value = "filetype", required = true) String fileType,
                             @RequestParam(value = "showColumnName", required = true) boolean showColumnName) throws Exception {

        ValueHolder valueHolder = new ValueHolder();

        if(!WebIntercept.checkKey(request)){
            valueHolder.put("code",-1);
            valueHolder.put("message","请勿连续点击！");
            return valueHolder.toJSONObject();
        }

        User user = (User) ShiroSessionUtil.getAttribute("user");
        QuerySessionImpl session = new QuerySessionImpl(request);
        TableManager tm = session.getTableManager();
        String location = ResourceUtils.getURL("/home/admin/tmp/").getPath();
        String uuid = UUID.randomUUID().toString().replace("-", "");
        location = location + File.separator+"export" + File.separator + uuid + File.separator;

        File svrDir = new File(location);
        if (!svrDir.isDirectory()) {
            svrDir.mkdirs();
        }
        String fullFileName = location + fileName + fileType;
        String path = File.separator + "export" + File.separator + uuid + File.separator + fileName + fileType;


        JSONObject jo=JSON.parseObject(searchdata);

        JSONObject fixedcolumns=jo.getJSONObject("fixedcolumns");
        fixedcolumns=JSONObject.parseObject(fixedcolumns.toJSONString().replace("=",""));
        jo.put("fixedcolumns",fixedcolumns);
        Table table = null;
        int tableid=jo.getIntValue("tableid") ;

        if(tableid>0&&table==null) {
            table = tm.getTable(tableid);
        }
        if(jo.getString("table")!=null&&table==null) {
            table = tm.getTable(jo.getString("table"));
        }
        if(table==null) {
            throw  new NDSException("tablenull");
        }

        JSONObject solrResult = null;
        JSONObject param = (JSONObject)jo.clone();
        if(table.getTrigger("solr")!=null && !fixedcolumns.containsKey("ID")) {
            solrResult = solrSearch(table,param,tm,session);
        }

        QueryRequest query= AjaxUtils.parseQuery(param, session, user.getId(), user.getLocale(),(TableImpl) table);
        //列头
        JSONArray headers = query.SelectbyDisObj(true,user.getLocale());

        //表数据返回
        JSONObject result;
        JSONArray rows;
        int totalRowCount;
        if(table.getTrigger("solr")!=null && !fixedcolumns.containsKey("ID")) {
            totalRowCount = solrResult.getIntValue("total");
        }else{
            result = getData(table,query);
            totalRowCount = result.getIntValue("totalRowCount");
            jo.put("range",max_rowcount);
        }

        if(totalRowCount > MAX_REPORT_LINES){
            totalRowCount = MAX_REPORT_LINES;
        }

        int pages = totalRowCount/max_rowcount;
        if(totalRowCount % max_rowcount != 0){
            pages = pages + 1;
        }

        try {
            if (fileType.endsWith(".xls")) {
                // inp = new FileInputStream(FilePath);
                Workbook wb = (Workbook) new HSSFWorkbook();
                Sheet sheet = wb.createSheet(table.getName());

                CellStyle headStyle = getStyle(wb,true,false,"STRING");
                CellStyle strStyle = getStyle(wb,false,false,"STRING");
                CellStyle numStyle = getStyle(wb,false,false,"NUMBER");

                Row excel_row;
                Cell cell;

                log.debug("export ->"+headers.toJSONString());
                for (int i = 0; i < headers.size(); i++) {
                    JSONObject header = headers.getJSONObject(i);
                    if("ID".equalsIgnoreCase(header.getString("colname")) ||
                            "image".equalsIgnoreCase(header.getString("display")) ||
                            "file".equalsIgnoreCase(header.getString("display"))){
                        headers.remove(i);
                        i--;
                    }
                }

                if (showColumnName) {
                    excel_row = sheet.createRow(0);
                    for (int i = 0; i < headers.size(); i++) {
                        JSONObject header = headers.getJSONObject(i);
                        cell = excel_row.createCell((short) i);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(headStyle);
                        String colName = header.getString("name");
                        cell.setCellValue(colName);
                        sheet.setColumnWidth((short)i,(colName.getBytes().length*256));

                    }
                }

                for(int page=0; page < pages; page++){
                    jo.put("startindex",max_rowcount*page);
                    param = (JSONObject)jo.clone();
                    if(table.getTrigger("solr")!=null && !fixedcolumns.containsKey("ID")) {
                        solrSearch(table,param,tm,session);
                    }
                    query= AjaxUtils.parseQuery(param, session, user.getId(), user.getLocale(),table);
                    result=getData(table,query);
                    rows = result.getJSONArray("row");

                    for(int row=0; rows != null && row<rows.size(); row++) {
                        JSONObject rs = (JSONObject) rows.get(row);
                        excel_row = sheet.createRow(page*max_rowcount+row+1);
                        for (int col = 0; col < headers.size(); col++) {
                            JSONObject header = headers.getJSONObject(col);
                            cell = excel_row.createCell(col);
                            switch (header.getString("type")) {
                                case "STRING":
                                    if(rs.get(header.getString("colname")) != null){
                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        if(header.getString("display").equalsIgnoreCase("OBJ_DATENUMBER") && Validator.isNotNull(s) && s.length() == 8){
                                            StringBuffer sb = new StringBuffer();
                                            sb.append(s.substring(0,4)).append("-")
                                                    .append(s.substring(4,6)).append("-")
                                                    .append(s.substring(6,8));
                                            s = sb.toString();
                                        }
                                        cell.setCellValue(s);
                                    }
                                    break;
                                case "NUMBER":
                                    if((header.getString("display").equals("select") ||
                                            header.getString("display").equals("check")) &&
                                            rs.get(header.getString("colname")) != null){

                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);

                                    } else if(rs.get(header.getString("colname")) != null) {
                                        Double d = rs.getJSONObject(header.getString("colname")).getDouble("val");
                                        if(d != null){
                                            cell.setCellValue(d);
                                            cell.setCellStyle(numStyle);
                                        }

                                    }
                                    break;
                                default:
                                    log.debug("Find at cell(" + (page*max_rowcount+row+1) + "," + (col + 1) + ") type is invalid");
                            }

                        }
                    }

                }
                FileOutputStream fileOut = new FileOutputStream(fullFileName);
                wb.write(fileOut);
                fileOut.close();

            } else if (fileType.endsWith(".xlsx")) {

                SXSSFWorkbook wb = new SXSSFWorkbook(100);
                wb.setCompressTempFiles(true);
                Sheet sheet = wb.createSheet(table.getName());

                CellStyle headStyle = getStyle(wb,true,false,"STRING");
                CellStyle strStyle = getStyle(wb,false,false,"STRING");
                CellStyle numStyle = getStyle(wb,false,false,"NUMBER");

                Row excel_row;
                Cell cell;

                log.debug("export ->"+headers.toJSONString());
                for (int i = 0; i < headers.size(); i++) {
                    JSONObject header = headers.getJSONObject(i);
                    if("ID".equalsIgnoreCase(header.getString("colname")) ||
                            "image".equalsIgnoreCase(header.getString("display")) ||
                            "file".equalsIgnoreCase(header.getString("display"))){
                        headers.remove(i);
                        i--;
                    }
                }

                if (showColumnName) {
                    excel_row = sheet.createRow(0);
                    for (int i = 0; i < headers.size(); i++) {
                        JSONObject header = headers.getJSONObject(i);
                        cell = excel_row.createCell((short) i);
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellStyle(headStyle);
                        String colName = header.getString("name");
                        cell.setCellValue(colName);
                        sheet.setColumnWidth((short)i,(colName.getBytes().length*256));

                    }
                }

                for(int page=0; page < pages; page++) {
                    jo.put("startindex",max_rowcount*page);
                    param = (JSONObject)jo.clone();
                    if(table.getTrigger("solr")!=null && !fixedcolumns.containsKey("ID")) {
                        solrSearch(table,param,tm,session);
                    }
                    query= AjaxUtils.parseQuery(param, session, user.getId(), user.getLocale(),table);
                    result=getData(table,query);
                    rows = result.getJSONArray("row");


                    for(int row=0; rows != null && row<rows.size(); row++) {
                        JSONObject rs = (JSONObject) rows.get(row);
                        excel_row = sheet.createRow(page*max_rowcount+row+1);
                        for (int col = 0; col < headers.size(); col++) {
                            JSONObject header = headers.getJSONObject(col);
                            cell = excel_row.createCell(col);
                            switch (header.getString("type")) {
                                case "STRING":
                                    if(rs.get(header.getString("colname")) != null) {
                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        if(header.getString("display").equalsIgnoreCase("OBJ_DATENUMBER") && Validator.isNotNull(s) &&  s.length() == 8){
                                            StringBuffer sb = new StringBuffer();
                                            sb.append(s.substring(0,4)).append("-")
                                                    .append(s.substring(4,6)).append("-")
                                                    .append(s.substring(6,8));
                                            s = sb.toString();
                                        }
                                        cell.setCellValue(s);
                                    }
                                    break;
                                case "NUMBER":

                                    if((header.getString("display").equals("select") ||
                                            header.getString("display").equals("check")) &&
                                            rs.get(header.getString("colname")) != null){

                                        String s = rs.getJSONObject(header.getString("colname")).getString("val");
                                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                        cell.setCellStyle(strStyle);
                                        cell.setCellValue(s);

                                    } else if(rs.get(header.getString("colname")) != null) {
                                        Double d = rs.getJSONObject(header.getString("colname")).getDouble("val");
                                        if(d != null){
                                            cell.setCellValue(d);
                                            cell.setCellStyle(numStyle);
                                        }
                                    }
                                    break;
                                default:
                                    log.debug("Find at cell(" + (page*max_rowcount+row+1) + "," + (col + 1) + ") type is invalid");
                            }

                        }

                        if (row % 100 == 0) {
                            ((SXSSFSheet) sheet).flushRows(100); // retain 100 last rows and flush all others
                        }
                    }
                }

                FileOutputStream fileOut = new FileOutputStream(fullFileName);
                wb.write(fileOut);
                fileOut.close();
            }
        } catch (Exception e) {
            log.error("Could not export to excel file:" + location + File.separator + fileName, e);
            throw new NDSException(e.getMessage(),e);

        }

        valueHolder.put("code",0);
        valueHolder.put("data",path);
        return valueHolder.toJSONObject();

    }



    private  CellStyle getStyle(Workbook wb, boolean isBold,boolean isnotnull,String type) {
        // Create a new font and alter it.
        Font font = wb.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        if (isBold) {
            font.setColor((short) HSSFColor.DARK_BLUE.index);
            font.setBoldweight(font.BOLDWEIGHT_BOLD);
        }

        if (isnotnull) {
            font.setColor((short) HSSFColor.RED.index);
        }
        // Fonts are set into a style so create a new one to use.
        CellStyle style = wb.createCellStyle();
        style.setFont(font);

        DataFormat format = wb.createDataFormat();

        if(type.equalsIgnoreCase("STRING")){
            style.setDataFormat(format.getFormat("text"));
        }else if(type.equalsIgnoreCase("NUMBER")){
            style.setDataFormat(format.getFormat("General"));
        }

        return style;
    }


    @RequestMapping(path = "/p/cs/download",method = {RequestMethod.POST,RequestMethod.GET})
    public void download(HttpServletRequest request,HttpServletResponse response,
                         @RequestParam(value = "filename", required = true) String filename) throws Exception {

        String location = ResourceUtils.getURL("/home/admin/tmp/").getPath();
        String fullFileName = location + filename;
        File file = new File(fullFileName);

        if (!file.exists()) {
            response.sendError(404, "File not found!");
            return;
        }
        String retFileName = URLEncoder.encode(filename.substring(filename.lastIndexOf("/")+1),"utf-8");
        // 读到流中
        InputStream in = new FileInputStream(file);// 文件的存放路径
        // 设置输出的格式
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.addHeader("content-disposition", "attachment;filename=" + retFileName);
        // 循环取出流中的数据
        byte[] b = new byte[1024];
        int len;
        try {
            while ((len = in.read(b)) > 0) {
                response.getOutputStream().write(b, 0, len);
            }
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public JSONObject solrSearch(Table table, JSONObject jo, TableManager tm, QuerySession qsession) throws Exception {

        JSONObject result = new JSONObject();
        //增加 OPENSEARCH
        DefaultWebEvent event = new DefaultWebEvent("solr",new HashMap());
        log.debug(table.getTrigger("solr").toString());
        event.put("command",table.getTrigger("solr").toString());
        if (Validator.isNotNull(jo.getJSONObject("fixedcolumns"))) {

            JSONObject fixedcolumns=jo.getJSONObject("fixedcolumns");
            JSONArray orderby = jo.getJSONArray("orderby")==null?new JSONArray():jo.getJSONArray("orderby");
            if(orderby.isEmpty()){
                JSONObject ordobj=new JSONObject();
                ordobj.put("name",table.getName()+"."+table.getPrimaryKey().getName());
                ordobj.put("asc",true);
                orderby.add(ordobj);
            }
            JSONArray searchtype=new JSONArray();
            for (String key : fixedcolumns.keySet()) {
                JSONObject po = new JSONObject();
                ColumnLink cl = new ColumnLink(table.getName() + "." + key, tm);
                Column column = cl.getColumns()[0];
                if(Validator.isNotNull( column.getSearchtype())) {
                    po.put("name",key);
                    po.put("type",column.getSearchtype());
                    searchtype.add(po);
                }
            }

            JSONObject sjo=new JSONObject();
            sjo.put("fixedcolumns",fixedcolumns);
            sjo.put("orderby",orderby);
            sjo.put("searchtype",searchtype);
            sjo.put("range",max_rowcount);
            sjo.put("startindex",jo.getIntValue("startindex"));
            sjo.put("issql",true);

            log.debug("export excel solr param->" + sjo.toJSONString());

            event.put("param", sjo);

            ValueHolder vh = handle.process(sjo, qsession, event, "solr");

            log.debug("export excel solr result->" + vh.toJSONObject());


            int code = Tools.getInt(vh.get("code"), 0);
            if (code == 0) {
                JSONObject jop=new JSONObject();
                jop.put("ID",vh.get("IDS"));
                jo.put("fixedcolumns",jop);
                jo.put("range",max_rowcount);
                jo.put("startindex",0);

                result.put("total",Tools.getInt(vh.get("TOTAL"),0));
                result.put("rowcount",Tools.getInt(vh.get("ROWCOUNT"),0));
                return result;

            }else{
                throw new NDSException("solr is error!");
            }
        }
        return null;
    }

}
